<!DOCTYPE html>
<html lang="en">

<!-- Head tag (contains Google-Analytics、Baidu-Tongji)-->
<head>
  <!-- Google Analytics -->
  

  <!-- Baidu Tongji -->
  
    <script type="text/javascript">
      // Originial
      var _hmt = _hmt || [];
      (function () {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?cdf616a127124dd7ff798963125b304d";
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
  

  <!-- Baidu Push -->
  
    <script>
      (function () {
        var bp = document.createElement('script');
        var curProtocol = window.location.protocol.split(':')[0];
        if (curProtocol === 'https') {
          bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
        } else {
          bp.src = 'http://push.zhanzhang.baidu.com/push.js';
        }
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(bp, s);
      })();
    </script>
  

  <meta charset="utf-8"/>
  <meta http-equiv="X-UA-Compatible" content="IE=edge"/>

  <meta name="google-site-verification" content="lxDfCplOZbIzjhG34NuQBgu2gdyRlAtMB4utP5AgEBc"/>
  <meta name="baidu-site-verification" content="PpzM9WxOJU"/>

  <meta name="viewport" content="width=device-width, initial-scale=1"/>
  <meta name="description" content="It&#39;s an IT blog..."/>
  <meta name="keyword" content="zhuang,IT  blog,Blog"/>
  <link rel="shortcut icon" href="/img/avatar/java-head.png"/>

  <!-- Place this tag in your head or just before your close body tag. -->
  <script async="async" defer="defer" src="https://buttons.github.io/buttons.js"></script>

  
    <!-- Bootstrap Core CSS -->
    <link rel="stylesheet" href="/css/bootstrap.min.css"/>

    <!-- Custom CSS -->
    <link rel="stylesheet" href="/css/beantech.min.css"/>

    <!-- Pygments Highlight CSS -->
    <link rel="stylesheet" href="/css/highlight.css"/>
    <link rel="stylesheet" href="/css/widget.css"/>
    <link rel="stylesheet" href="/css/rocket.css"/>
    <link rel="stylesheet" href="/css/signature.css"/>
    <link rel="stylesheet" href="/css/catalog.css"/>
    <link rel="stylesheet" href="/css/livemylife.css"/>

    
      <!-- wave start -->
      <link rel="stylesheet" href="/css/wave.css"/>
      <!-- wave end -->
    

    
      <!-- top start (article top hot config) -->
      <link rel="stylesheet" href="/css/top.css"/>
      <!-- top end -->
    

    
      <!-- ThemeColor start -->
      <link rel="stylesheet" href="/css/scroll.css"/>
      <!-- ThemeColor end -->
    

    
      <!-- viewer start (Picture preview) -->
      <link rel="stylesheet" href="/css/viewer.min.css"/>
      <!-- viewer end -->
    

    
      <!-- Search start -->
      <link rel="stylesheet" href="/css/search.css"/>
      <!-- Search end -->
    

    
      <!-- ThemeColor start -->
      <link rel="stylesheet" href="/css/themecolor.css"/>
      <!-- ThemeColor end -->
    

    

    
  

  <!-- Custom Fonts -->
  <!-- <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css" rel="stylesheet" type="text/css"> -->
  <!-- Hux change font-awesome CDN to qiniu -->
  <link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/4.5.0/css/font-awesome.min.css" type="text/css">
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">

  <!-- Hux Delete, sad but pending in China <link href='http://fonts.googleapis.com/css?family=Lora:400,700,400italic,700italic' rel='stylesheet' type='text/css'> <link
  href='http://fonts.googleapis.com/css?family=Open+Sans:300italic,400italic,600italic,700italic,800italic,400,300,600,700,800' rel='stylesheet' type='text/ css'> -->

  <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
  <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
  <!--[if lt IE 9]> <script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script> <script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script> <![endif]-->

  <!-- ga & ba script hoook -->
  <link rel="canonical" href="http://yoursite-url/cn/mysql/SQL教程/">
  <title>
    
      SQL基础 - either thin | or die
    
  </title>
<meta name="generator" content="Hexo 5.4.0"></head>


<!-- hack iOS CSS :active style -->

	<body ontouchstart="" class="body--light body--dark">


		<!-- ThemeColor -->
		
		<!-- ThemeColor -->
<style type="text/css">
  .body--light {
    --light-mode: none;
    --dark-mode: block;
  }
  .body--dark {
    --light-mode: block;
    --dark-mode: none;
  }
  i.mdui-icon.material-icons.light-mode {
    display: var(--light-mode);
  }
  i.mdui-icon.material-icons.dark-mode {
    display: var(--dark-mode);
  }
</style>
<div class="toggle" onclick="document.body.classList.toggle('body--dark')">
  <i class="mdui-icon material-icons light-mode"></i>
  <i class="mdui-icon material-icons dark-mode"></i>
</div>
<script>
  //getCookieValue
  function getCookieValue(a) {
    var b = document.cookie.match('(^|[^;]+)\\s*' + a + '\\s*=\\s*([^;]+)');
    return b
      ? b.pop()
      : '';
  }
  let themeMode = 'dark';
  if (getCookieValue('sb-color-mode') && (getCookieValue('sb-color-mode') !== themeMode)) {
    let dbody = document.body.classList;
    themeMode === 'dark' ? dbody.remove('body--dark') : dbody.add('body--dark');
  }

  //setCookieValue
  var toggleBtn = document.querySelector(".toggle");
  toggleBtn.addEventListener("click", function () {
    var e = document.body.classList.contains("body--dark");
    var cookieString = e
      ? "dark"
      : "light";
    var exp = new Date();
    exp.setTime(exp.getTime() + 3 * 24 * 60 * 60 * 1000); //3天过期
    document.cookie = "sb-color-mode=" + cookieString + ";expires=" + exp.toGMTString() + ";path=/";
  });
</script>

		

		<!-- Gitter -->
		

		<!-- Navigation (contains search)-->
		<!-- Navigation -->
<nav class="navbar navbar-default navbar-custom navbar-fixed-top">
  <div class="container-fluid">
    <!-- Brand and toggle get grouped for better mobile display -->
    <div class="navbar-header page-scroll">
      <button type="button" class="navbar-toggle">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
      <a class="navbar-brand" href="/">平平淡淡</a>
    </div>

    <!-- Collect the nav links, forms, and other content for toggling -->
    <!-- Known Issue, found by Hux: <nav>'s height woule be hold on by its content. so, when navbar scale out, the <nav> will cover tags. also mask any touch event of tags, unfortunately. -->
    <div id="huxblog_navbar">
      <div class="navbar-collapse">
        <ul class="nav navbar-nav navbar-right">
          <li>
            <a href="/">首页</a>
          </li>

          
          
          
          
          <li>
            <a href="/archive/">
              
              归档
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/tags/">
              
              标签
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/categories/">
              
              分类
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/about/">
              
              关于
              
              
            </a>
          </li>
          
          

          
          <li>
            <a class="popup-trigger">
              <span class="search-icon"></span>搜索</a>
          </li>
          

          <!-- LangSelect -->
          
        </ul>
      </div>
    </div>
    <!-- /.navbar-collapse -->
  </div>
  <!-- /.container -->
</nav>
<!-- progress -->
<div id="progress">
  <div class="line" style="width: 0%;"></div>
</div>

<script>
  // Drop Bootstarp low-performance Navbar Use customize navbar with high-quality material design animation in high-perf jank-free CSS3 implementation
  var $body = document.body;
  var $toggle = document.querySelector('.navbar-toggle');
  var $navbar = document.querySelector('#huxblog_navbar');
  var $collapse = document.querySelector('.navbar-collapse');

  $toggle.addEventListener('click', handleMagic)

  function handleMagic(e) {
    if ($navbar.className.indexOf('in') > 0) {
      // CLOSE
      $navbar.className = " ";
      // wait until animation end.
      setTimeout(function() {
        // prevent frequently toggle
        if ($navbar.className.indexOf('in') < 0) {
          $collapse.style.height = "0px"
        }
      }, 400)
    } else {
      // OPEN
      $collapse.style.height = "auto"
      $navbar.className += " in";
    }
  }
</script>


		<!-- Post Header (contains intro-header、signature、wordcount、busuanzi、waveoverlay) -->
		<!-- Modified by Yu-Hsuan Yen -->
<!-- Post Header -->

  <style type="text/css">
    .body--light {
      /* intro-header */
      --intro-header-background-image-url-home: url('/img/header_img/home_bg1.jpg');
      --intro-header-background-image-url-post: url('/img/header_img/lml_bg.jpg');
      --intro-header-background-image-url-page: url('//img/header_img/lml_bg.jpg');
    }
    .body--dark {
      --intro-header-background-image-url-home: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('/img/header_img/home_bg1.jpg');
      --intro-header-background-image-url-post: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('/img/header_img/lml_bg.jpg');
      --intro-header-background-image-url-page: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('//img/header_img/lml_bg.jpg');
    }

    header.intro-header {
       /*post*/
        background-image: var(--intro-header-background-image-url-post);
        /* background-image: url('/img/header_img/lml_bg.jpg'); */
      
    }

    
      #signature {/*signature*/
        background-image: url('/img/signature/signature.png');
      }
    
  </style>





<header class="intro-header">
  <!-- Signature -->
  <div id="signature">
    <div class="container">
      <div class="row">
        <div class="col-lg-8 col-lg-offset-2 col-md-10 col-md-offset-1">
          
          <div class="post-heading">
            <div class="tags">
              
              <a class="tag" href="/tags/#数据库" title="数据库">数据库</a>
              
              <a class="tag" href="/tags/#mysql" title="mysql">mysql</a>
              
              <a class="tag" href="/tags/#SQL" title="SQL">SQL</a>
              
            </div>
            <h1>SQL基础</h1>
            <h2 class="subheading"></h2>
            <span class="meta">
              Posted by zhuang on
              2021-09-21
            </span>


            
            <!-- WordCount start -->
            <div class="blank_box"></div>
            <span class="meta">
              Estimated Reading Time <span class="post-count">23</span> Minutes
            </span>
            <div class="blank_box"></div>
            <span class="meta">
              Words <span class="post-count">5.7k</span> In Total
            </span>
            <div class="blank_box"></div>
            <!-- WordCount end -->
            
            
            <!-- 不蒜子统计 start -->
            <span class="meta" id="busuanzi_container_page_pv">
              Viewed <span id="busuanzi_value_page_pv"><i class="fa fa-spinner fa-spin"></i></span> Times
            </span>
            <!-- 不蒜子统计 end -->
            


          </div>
          
        </div>
      </div>
    </div>
  </div>

  
  <!-- waveoverlay start -->
  <div class="preview-overlay">
    <svg class="preview-waves" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 24 150 28" preserveAspectRatio="none" shape-rendering="auto">
      <defs>
        <path id="gentle-wave" d="M-160 44c30 0 58-18 88-18s 58 18 88 18 58-18 88-18 58 18 88 18 v44h-352z"></path>
      </defs>
      <g class="preview-parallax">
        <use xlink:href="#gentle-wave" x="48" y="0" fill=var(--gentle-wave1)></use>
        <use xlink:href="#gentle-wave" x="48" y="3" fill=var(--gentle-wave2)></use>
        <use xlink:href="#gentle-wave" x="48" y="5" fill=var(--gentle-wave3)></use>
        <use xlink:href="#gentle-wave" x="48" y="7" fill=var(--gentle-wave)></use>
      </g>
    </svg>
  </div>
  <!-- waveoverlay end -->
  

</header>



		<!-- Main Content (Post contains
	Pager、
	tip、
	socialshare、
	gitalk、gitment、disqus-comment、
	Catalog、
	Sidebar、
	Featured-Tags、
	Friends Blog、
	anchorjs、
	) -->
		<!-- Modify by Yu-Hsuan Yen -->
<!-- Post Content -->
<article>
  <div class="container">
    <div class="row">
      <!-- Post Container -->
      <div class="col-lg-8 col-lg-offset-1 col-md-10 col-md-offset-1 post-container">

        <h2 id="时间函数"><a href="#时间函数" class="headerlink" title="时间函数"></a>时间函数</h2><p>NOW()、CURDATE()、CURTIME()获取当前时间</p>
<ul>
<li>NOW() :返回当前日期和时间 格式:YYYY-MM-DD hh:mm:ss</li>
<li>CURDATE(): 返回当前日期   格式:YYYY-MM-DD</li>
<li><p>CURTIME(): 返回当前时间    格式: hh:mm:ss</p>
</li>
<li><p>DATE(time):提取time中的日期</p>
</li>
<li><p>TIME(time):提取time中的时间</p>
</li>
<li><p>EXTRACT(unit FROM date):提取指定的时间信息</p>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">extract</span>(unit <span class="keyword">FROM</span> <span class="type">date</span>)</span><br><span class="line"><span class="keyword">FROM</span> `<span class="keyword">table</span>`</span><br></pre></td></tr></table></figure>
<p>table: 表名</p>
<p>date: 日期</p>
<p>unit: 需要返回的时间部分（YEAR、MONTH、DAY、HOUR等）</p>
<ul>
<li>DATE_FORMAT(date, format): 格式化输出日期</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(<span class="type">date</span>, format)</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> DATE_FORMA(`created_at`, <span class="string">&#x27;%Y %m&#x27;</span>)</span><br></pre></td></tr></table></figure>
<div class="table-container">
<table>
<thead>
<tr>
<th style="text-align:left">格式</th>
<th style="text-align:left">描述</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:left">%a</td>
<td style="text-align:left">缩写星期名</td>
</tr>
<tr>
<td style="text-align:left">%b</td>
<td style="text-align:left">缩写月名</td>
</tr>
<tr>
<td style="text-align:left">%c</td>
<td style="text-align:left">月，数值</td>
</tr>
<tr>
<td style="text-align:left">%D</td>
<td style="text-align:left">带有英文前缀的月中的天</td>
</tr>
<tr>
<td style="text-align:left">%d</td>
<td style="text-align:left">月的天，数值(00-31)</td>
</tr>
<tr>
<td style="text-align:left">%e</td>
<td style="text-align:left">月的天，数值(0-31)</td>
</tr>
<tr>
<td style="text-align:left">%f</td>
<td style="text-align:left">微秒</td>
</tr>
<tr>
<td style="text-align:left">%H</td>
<td style="text-align:left">小时 (00-23)</td>
</tr>
<tr>
<td style="text-align:left">%h</td>
<td style="text-align:left">小时 (01-12)</td>
</tr>
<tr>
<td style="text-align:left">%I</td>
<td style="text-align:left">小时 (01-12)</td>
</tr>
<tr>
<td style="text-align:left">%i</td>
<td style="text-align:left">分钟，数值(00-59)</td>
</tr>
<tr>
<td style="text-align:left">%j</td>
<td style="text-align:left">年的天 (001-366)</td>
</tr>
<tr>
<td style="text-align:left">%k</td>
<td style="text-align:left">小时 (0-23)</td>
</tr>
<tr>
<td style="text-align:left">%l</td>
<td style="text-align:left">小时 (1-12)</td>
</tr>
<tr>
<td style="text-align:left">%M</td>
<td style="text-align:left">月名</td>
</tr>
<tr>
<td style="text-align:left">%m</td>
<td style="text-align:left">月，数值(00-12)</td>
</tr>
<tr>
<td style="text-align:left">%p</td>
<td style="text-align:left">AM 或 PM</td>
</tr>
<tr>
<td style="text-align:left">%r</td>
<td style="text-align:left">时间，12-小时（hh:mm:ss AM 或 PM）</td>
</tr>
<tr>
<td style="text-align:left">%S</td>
<td style="text-align:left">秒(00-59)</td>
</tr>
<tr>
<td style="text-align:left">%s</td>
<td style="text-align:left">秒(00-59)</td>
</tr>
<tr>
<td style="text-align:left">%T</td>
<td style="text-align:left">时间, 24-小时 (hh:mm:ss)</td>
</tr>
<tr>
<td style="text-align:left">%U</td>
<td style="text-align:left">周 (00-53) 星期日是一周的第一天</td>
</tr>
<tr>
<td style="text-align:left">%u</td>
<td style="text-align:left">周 (00-53) 星期一是一周的第一天</td>
</tr>
<tr>
<td style="text-align:left">%V</td>
<td style="text-align:left">周 (01-53) 星期日是一周的第一天，与 %X 使用</td>
</tr>
<tr>
<td style="text-align:left">%v</td>
<td style="text-align:left">周 (01-53) 星期一是一周的第一天，与 %x 使用</td>
</tr>
<tr>
<td style="text-align:left">%W</td>
<td style="text-align:left">星期名</td>
</tr>
<tr>
<td style="text-align:left">%w</td>
<td style="text-align:left">周的天 （0=星期日, 6=星期六）</td>
</tr>
<tr>
<td style="text-align:left">%X</td>
<td style="text-align:left">年，其中的星期日是周的第一天，4 位，与 %V 使用</td>
</tr>
<tr>
<td style="text-align:left">%x</td>
<td style="text-align:left">年，其中的星期一是周的第一天，4 位，与 %v 使用</td>
</tr>
<tr>
<td style="text-align:left">%Y</td>
<td style="text-align:left">年，4 位</td>
</tr>
<tr>
<td style="text-align:left">%y</td>
<td style="text-align:left">年，2 位</td>
</tr>
</tbody>
</table>
</div>
<ul>
<li>DATE_ADD() ：向日期添加指定的时间间隔</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_ADD(<span class="type">date</span>, INTERVAVL expr type)</span><br><span class="line"><span class="keyword">FROM</span> table_name</span><br></pre></td></tr></table></figure>
<p>date：指被操作的有效日期</p>
<p>expr：希望添加的时间间隔数值</p>
<p>type：MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR</p>
<ul>
<li><p>DATE_SUB() ：减少时间，与DATE_ADD()语法相似</p>
</li>
<li><p>DATEDIFF(时间1, 时间2)：日期差，默认只能计算天数差</p>
</li>
<li>TIMESTAMPDIFF(类型, 时间1, 时间2)：计算过两个日期相差的年、月、周、日，小时</li>
</ul>
<p>时间1 - 时间2</p>
<h2 id="约束"><a href="#约束" class="headerlink" title="约束"></a>约束</h2><h3 id="非空约束NOT-NULL"><a href="#非空约束NOT-NULL" class="headerlink" title="非空约束NOT NULL"></a>非空约束NOT NULL</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `Persons` (</span><br><span class="line">    `ID` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `LastName` <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `FirstName` <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `Age` <span class="type">int</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line">MODIFY `Age` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>;</span><br></pre></td></tr></table></figure>
<h3 id="唯一约束-UNIQUE"><a href="#唯一约束-UNIQUE" class="headerlink" title="唯一约束 UNIQUE"></a>唯一约束 UNIQUE</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE &#96;Persons&#96;</span><br><span class="line">(</span><br><span class="line">&#96;P_Id&#96; int NOT NULL,</span><br><span class="line">&#96;LastName&#96; varchar(255) NOT NULL,</span><br><span class="line">&#96;FirstName&#96; varchar(255),</span><br><span class="line">&#96;Address&#96; varchar(255),</span><br><span class="line">&#96;City&#96; varchar(255),</span><br><span class="line">UNIQUE (&#96;P_Id&#96;)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>命名UNIQUE约束，并定义多个列的UNIQUE约束</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line">(</span><br><span class="line">`P_Id` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">`LastName` <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">`FirstName` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">`Address` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">`City` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line"><span class="keyword">CONSTRAINT</span> uc_PersonID <span class="keyword">UNIQUE</span> (`P_Id`,`LastName`)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>修改表的UNIQUE约束</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">UNIQUE</span>（`P_Id`）</span><br></pre></td></tr></table></figure>
<h3 id="主键约束-PRIMARY-KEY"><a href="#主键约束-PRIMARY-KEY" class="headerlink" title="主键约束 PRIMARY KEY"></a>主键约束 PRIMARY KEY</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line">(</span><br><span class="line">    `P_Id` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `LastName` <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `FirstName` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">    `Address` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">    `City` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">    <span class="keyword">PRIMARY</span> KEY (`P_Id`)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>如需命名并定义多个列的PRIMARY KEY 约束</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line">(</span><br><span class="line">    `P_Id` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `LastName` <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `FirstName` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">    `Address` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">    `City` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">    <span class="keyword">CONSTRAINT</span> pk_PersonID <span class="keyword">PRIMARY</span> KEY (`P_Id`,`LastName`)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>修改表时添加主键约束</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">PRIMARY</span> KEY (`P_Id`)</span><br></pre></td></tr></table></figure>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> pk_PersonID <span class="keyword">PRIMARY</span> KEY (`P_Id`,`LastName`)</span><br></pre></td></tr></table></figure>
<p>撤销</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">PRIMARY</span> KEY</span><br></pre></td></tr></table></figure>
<h3 id="外键约束-FOREIGN-KEY"><a href="#外键约束-FOREIGN-KEY" class="headerlink" title="外键约束 FOREIGN KEY"></a>外键约束 FOREIGN KEY</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `Orders`</span><br><span class="line">(</span><br><span class="line">`O_Id` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">`OrderNo` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">`P_Id` <span class="type">int</span>,</span><br><span class="line"><span class="keyword">PRIMARY</span> KEY (O_Id),</span><br><span class="line"><span class="keyword">FOREIGN</span> KEY (P_Id) <span class="keyword">REFERENCES</span> Persons(P_Id)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>命名FOREIGN KEY约束，并定义多个列的FOREIGN KEY约束</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `Orders`</span><br><span class="line">(</span><br><span class="line">`O_Id` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">`OrderNo` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">`P_Id` <span class="type">int</span>,</span><br><span class="line"><span class="keyword">PRIMARY</span> KEY (O_Id),</span><br><span class="line"><span class="keyword">CONSTRAINT</span> fk_PerOrders <span class="keyword">FOREIGN</span> KEY (P_Id)</span><br><span class="line"><span class="keyword">REFERENCES</span> Persons(P_Id)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>CONSTRAINT 表约束，后接约束名称，常用于创建约束和删除约束</p>
</blockquote>
<p>修改表的FOREIGN KEY 约束</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `Orders`</span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">FOREIGN</span> KEY (P_Id)</span><br><span class="line"><span class="keyword">REFERENCES</span> Persons(P_Id)</span><br></pre></td></tr></table></figure>
<h3 id="检查约束-CHECK"><a href="#检查约束-CHECK" class="headerlink" title="检查约束 CHECK"></a>检查约束 CHECK</h3><p>CHECK约束用于限制列中的值的范围，评估插入或修改后的值。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[<span class="keyword">CONSTRAINT</span> <span class="operator">&lt;</span><span class="keyword">constraint</span> name<span class="operator">&gt;</span>] <span class="keyword">CHECK</span> (<span class="operator">&lt;</span><span class="keyword">condition</span><span class="operator">&gt;</span>)</span><br></pre></td></tr></table></figure>
<blockquote>
<ul>
<li>约束关键字CONSTRAINT：表示其后面接的内容为约束</li>
<li>constraint name：约束名称</li>
<li>关键字CHECK： 表示检查约束</li>
<li>condition：被约束内容</li>
</ul>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `courses`</span><br><span class="line">(</span><br><span class="line">`id` <span class="type">int</span>,</span><br><span class="line">`name` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">`student_count` <span class="type">int</span>,</span><br><span class="line">`created_at` <span class="type">date</span>,</span><br><span class="line">`teacher_id` <span class="type">int</span>,</span><br><span class="line"><span class="keyword">CHECK</span> (`student_count` <span class="operator">&gt;</span> <span class="number">0</span> <span class="keyword">AND</span> `teacher_id` <span class="operator">&gt;</span> <span class="number">0</span>)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<h3 id="默认约束-DEFAULT"><a href="#默认约束-DEFAULT" class="headerlink" title="默认约束 DEFAULT"></a>默认约束 DEFAULT</h3><ul>
<li>DEFAULT：约束用于向列中插入默认值</li>
</ul>
<blockquote>
<p>&lt;字段名&gt; &lt;数据类型&gt; DEFAULT &lt;默认值&gt;</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line">(</span><br><span class="line">    `P_Id` <span class="type">int</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `LastName` <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span>,</span><br><span class="line">    `FirstName` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">    `Address` <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">    `City` <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">DEFAULT</span> <span class="string">&#x27;Sandnes&#x27;</span></span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>修改表的DEFAULT约束</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `Persons`</span><br><span class="line"><span class="keyword">ALTER</span> `City` <span class="keyword">SET</span> <span class="keyword">DEFAULT</span> <span class="string">&#x27;SANDNES&#x27;</span></span><br></pre></td></tr></table></figure>
<h2 id="联结"><a href="#联结" class="headerlink" title="联结"></a>联结</h2><p>JOIN连接子句</p>
<ul>
<li>INNER JOIN：内连接，如果表中有至少一个匹配，则返回行</li>
<li>OUTER JOIN：外连接<ul>
<li>LEFT JOIN：左连接，即时右表没有匹配，也从左表返回所有的行</li>
<li>RIGHT JOIN：右连接，即时左表中没有匹配，也从右表返回所有的行</li>
<li>FULL JOIN：全连接，只要其中一个表中存在匹配，则返回行</li>
<li>CROSS JOIN：交叉连接，两个表数据一一对应，返回结果的行数等于两个表行数的乘积。</li>
</ul>
</li>
</ul>
<p>内连接INNER JOIN</p>
<p>内连接就是取两个表的交集，返回的结果就是连接的两张表中都满足条件的部分。</p>
<p><img src="innerjoin.png" alt=""></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">FROM</span> table1</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> table2</span><br><span class="line"><span class="keyword">ON</span> table1.common_field <span class="operator">=</span> table2.common_field</span><br></pre></td></tr></table></figure>
<p>外连接 OUTER JOIN</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column_name <span class="number">1</span>,column_name <span class="number">2</span> ... column_name n</span><br><span class="line">    <span class="keyword">FROM</span> table1</span><br><span class="line">        <span class="keyword">LEFT</span> <span class="operator">|</span> <span class="keyword">RIGHT</span> <span class="operator">|</span> <span class="keyword">FULL</span>  (<span class="keyword">OUTER</span>) <span class="keyword">JOIN</span> table2</span><br><span class="line">        <span class="keyword">ON</span> <span class="keyword">CONDITION</span>;</span><br></pre></td></tr></table></figure>
<blockquote>
<p>Mysql数据库不支持全连，想实现全连接可以使用UNION ALL来进行左连接和右连接结果进行全连接</p>
</blockquote>
<p>UNION：联合，两次或多次查询结果合并起来</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column_name <span class="number">1</span>,column_name <span class="number">2</span> ... column_name n</span><br><span class="line">    <span class="keyword">FROM</span> table1</span><br><span class="line">        <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> table2 <span class="keyword">ON</span> <span class="keyword">CONDITION</span> </span><br><span class="line"><span class="keyword">UNION</span></span><br><span class="line"><span class="keyword">SELECT</span> column_name <span class="number">1</span>,column_name <span class="number">2</span> ... column_name n</span><br><span class="line">    <span class="keyword">FROM</span> table1</span><br><span class="line">        <span class="keyword">RIGHT</span> <span class="keyword">JOIN</span> table2 <span class="keyword">ON</span> <span class="keyword">CONDITION</span> ;</span><br></pre></td></tr></table></figure>
<p>交叉连接CROSS JOIN</p>
<p>隐式交叉连接</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> `table1`.`column1`, `table2`.`column2`...</span><br><span class="line"><span class="keyword">FROM</span> `table1`,`table2`;</span><br></pre></td></tr></table></figure>
<p>显示交叉连接</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> `table1`.`column1`, `table2`.`column2`...</span><br><span class="line"><span class="keyword">FROM</span> `table1`</span><br><span class="line"><span class="keyword">CROSS</span> <span class="keyword">JOIN</span> `table2`;</span><br></pre></td></tr></table></figure>
<h2 id="分组查询"><a href="#分组查询" class="headerlink" title="分组查询"></a>分组查询</h2><h3 id="GROUP-BY-子句"><a href="#GROUP-BY-子句" class="headerlink" title="GROUP BY 子句"></a>GROUP BY 子句</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> `column_name`, aggregate_function(`column_name`)</span><br><span class="line"><span class="keyword">FROM</span> `table_name`</span><br><span class="line"><span class="keyword">WHERE</span> `column_name` operator <span class="keyword">value</span></span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> `column_name`;</span><br></pre></td></tr></table></figure>
<h3 id="HAVING-子句"><a href="#HAVING-子句" class="headerlink" title="HAVING 子句"></a>HAVING 子句</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span>   `column_name`, aggregate_function(`column_name`) </span><br><span class="line"><span class="keyword">FROM</span>     `table_name` </span><br><span class="line"><span class="keyword">WHERE</span>    `column_name` operator <span class="keyword">value</span> </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> `column_name` </span><br><span class="line"><span class="keyword">HAVING</span>   aggregate_function(`column_name`) operator <span class="keyword">value</span>;</span><br></pre></td></tr></table></figure>
<p>查询需要联合多个表时，一是使用连接查询，二是子查询</p>
<h3 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h3><p>select语句中的子查询</p>
<p>当一个查询是另一个查询的条件时，称为子查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> `column_name(s)`</span><br><span class="line"><span class="keyword">FROM</span> `table_name`</span><br><span class="line"><span class="keyword">WHERE</span> `column_name` OPERATOR (</span><br><span class="line">    <span class="keyword">SELECT</span> `column_name(s)`</span><br><span class="line">    <span class="keyword">FROM</span> `table_name`</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>insert语句中的子查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `table_name`</span><br><span class="line">	<span class="keyword">SELECT</span> `colnum_name(s)`</span><br><span class="line">	<span class="keyword">FROM</span> `table_name`</span><br><span class="line">	[ <span class="keyword">WHERE</span> <span class="keyword">VALUE</span> OPERATOR ]</span><br></pre></td></tr></table></figure>
<blockquote>
<p>INSERT 语句中的子查询其实是将一个表中查询到的数据“复制”到另一个表中，由于主键具有唯一性，如果需要仅在单张表中使用 INSERT 子查询，只能在无主键的单张表中进行操作，否则，需要有两张表（如只一张表，则需新建一张表）。</p>
</blockquote>
<p>update语句中的子查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">UPDATE `table_name` </span><br><span class="line"><span class="keyword">SET</span> `column_name` <span class="operator">=</span> `new_value`</span><br><span class="line"><span class="keyword">WHERE</span> `column_name` OPERATOR </span><br><span class="line">   (<span class="keyword">SELECT</span> `column_name`</span><br><span class="line">   <span class="keyword">FROM</span> `table_name`</span><br><span class="line">   [<span class="keyword">WHERE</span>] )</span><br></pre></td></tr></table></figure>
<blockquote>
<p>在 UPDATE 语句的子查询中，子查询 SELECT 语句所用的表和 UPDATE 语句所要更改的表<strong>不能</strong>是同一张表！</p>
</blockquote>
<p>delete语句中的子查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> `table_name`</span><br><span class="line"><span class="keyword">WHERE</span> `column_name` OPERATOR </span><br><span class="line">   (<span class="keyword">SELECT</span> `column_name`</span><br><span class="line">   <span class="keyword">FROM</span> `table_name`  </span><br><span class="line">   [<span class="keyword">WHERE</span>] )</span><br></pre></td></tr></table></figure>
<blockquote>
<p>在 DELETE 语句的子查询中，子查询 SELECT 语句所用的表和 DELETE 语句所要更改的表<strong>不能</strong>是同一张表！</p>
</blockquote>
<h3 id="内联视图子查询"><a href="#内联视图子查询" class="headerlink" title="内联视图子查询"></a>内联视图子查询</h3><h3 id="多行子查询"><a href="#多行子查询" class="headerlink" title="多行子查询"></a>多行子查询</h3><h4 id="IN"><a href="#IN" class="headerlink" title="IN"></a>IN</h4><p>将子查询返回的集合和外层查询得到的集合进行交集运算，结果可以是零值，也可是多值。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> `column_name`</span><br><span class="line"><span class="keyword">FROM</span> `table_name`</span><br><span class="line"><span class="keyword">WHERE</span> `column_name` <span class="keyword">IN</span>(</span><br><span class="line">    <span class="keyword">SELECT</span> `column_name`</span><br><span class="line">    <span class="keyword">FROM</span> `table_name`</span><br><span class="line">    <span class="keyword">WHERE</span> `column_name` <span class="operator">=</span> <span class="keyword">VALUE</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>NOT IN</p>
<p>子查询返回的集合和外层查询得到的集合进行差集运算</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> `name`</span><br><span class="line"><span class="keyword">FROM</span> `courses`</span><br><span class="line"><span class="keyword">WHERE</span> `teacher_id` <span class="keyword">NOT</span> <span class="keyword">IN</span> (</span><br><span class="line">	<span class="keyword">SELECT</span> `id`</span><br><span class="line">	<span class="keyword">FROM</span> `teachers`</span><br><span class="line">	<span class="keyword">WHERE</span> `age` <span class="operator">&gt;</span> <span class="number">20</span></span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<h4 id="ANY"><a href="#ANY" class="headerlink" title="ANY"></a>ANY</h4><h4 id="ALL"><a href="#ALL" class="headerlink" title="ALL"></a>ALL</h4><h3 id="多列子查询"><a href="#多列子查询" class="headerlink" title="多列子查询"></a>多列子查询</h3><ul>
<li><p>单行多列子查询：引用子查询结果可用单行比较符号（=、&gt;、&lt;、&gt;=、&lt;=、&lt;&gt;）</p>
</li>
<li><p>多行多列子查询：引用子查询结果必须用多行比较符（IN、ANY、ALL等）来进行比较</p>
</li>
</ul>
<p><strong>现需要找到每个国家年龄最大的教师，请编写 SQL 语句实现多列子查询</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> `name`, `age`, `country` </span><br><span class="line"><span class="keyword">FROM</span> `teachers` </span><br><span class="line"><span class="keyword">WHERE</span> (`country`, `age`) <span class="keyword">IN</span> ( </span><br><span class="line">        <span class="keyword">SELECT</span> `country`, <span class="built_in">MAX</span>(`age`) </span><br><span class="line">        <span class="keyword">FROM</span> `teachers` </span><br><span class="line">        <span class="keyword">GROUP</span> <span class="keyword">BY</span> `country` </span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<h4 id="HAVING子句"><a href="#HAVING子句" class="headerlink" title="HAVING子句"></a>HAVING子句</h4><p>表示要进行分组过滤，被用作行组选择的一部分，一般返回单行单列的数据</p>
<p><strong>现需要计算每位教师所开课程的平均学生人数与全部课程的平均学生人数，比较其大小，最后返回超过全部课程平均学生人数的教师姓名，请编写相应的 SQL 语句实现</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> `name`</span><br><span class="line"><span class="keyword">FROM</span> `teachers`</span><br><span class="line"><span class="keyword">WHERE</span> `id` <span class="keyword">IN</span> (</span><br><span class="line">	<span class="keyword">SELECT</span> `teacher_id`</span><br><span class="line">	<span class="keyword">FROM</span> `courses`</span><br><span class="line">	<span class="keyword">GROUP</span> <span class="keyword">BY</span> `teacher_id`</span><br><span class="line">	<span class="keyword">HAVING</span> <span class="built_in">AVG</span>(`student_count`) <span class="operator">&gt;</span> (</span><br><span class="line">		<span class="keyword">SELECT</span> <span class="built_in">AVG</span>(`student_count`)</span><br><span class="line">		<span class="keyword">FROM</span> `courses`</span><br><span class="line">	)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<h2 id="MySQL事务"><a href="#MySQL事务" class="headerlink" title="MySQL事务"></a>MySQL事务</h2><p>MySQL事务允许执行一组MySQL操作，以确保数据库从不包含部分操作的结果。在一组操作中，如果其中一个操作失败，就会发生回滚，将数据库恢复其原始状态。如果没有发生错误，整个语句集就会提交到数据库中。</p>
<p>MySQL事务语句</p>
<ul>
<li>启动一个事物，使用<code>START TRANSACTION</code>语句。<code>BEGIN WORK</code>时<code>START TRANSACTION</code>的别名</li>
<li>提交事务并使其变化永久化，使用<code>COMMIT</code>语句</li>
<li>回滚事务并取消变化，使用<code>ROLLBACK</code>语句</li>
<li>禁用或启用当前事务的自动提交模式，使用<code>SET autocommit</code>语句。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1. start a new transaction</span></span><br><span class="line"><span class="keyword">START</span> TRANSACTION;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 2. Get the latest order number</span></span><br><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">    <span class="variable">@orderNumber</span>:<span class="operator">=</span><span class="built_in">MAX</span>(orderNUmber)<span class="operator">+</span><span class="number">1</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    orders;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 3. insert a new order for customer 145</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> orders(orderNumber,</span><br><span class="line">                   orderDate,</span><br><span class="line">                   requiredDate,</span><br><span class="line">                   shippedDate,</span><br><span class="line">                   status,</span><br><span class="line">                   customerNumber)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="variable">@orderNumber</span>,</span><br><span class="line">       <span class="string">&#x27;2005-05-31&#x27;</span>,</span><br><span class="line">       <span class="string">&#x27;2005-06-10&#x27;</span>,</span><br><span class="line">       <span class="string">&#x27;2005-06-11&#x27;</span>,</span><br><span class="line">       <span class="string">&#x27;In Process&#x27;</span>,</span><br><span class="line">        <span class="number">145</span>);</span><br><span class="line">        </span><br><span class="line"><span class="comment">-- 4. Insert order line items</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> orderdetails(orderNumber,</span><br><span class="line">                         productCode,</span><br><span class="line">                         quantityOrdered,</span><br><span class="line">                         priceEach,</span><br><span class="line">                         orderLineNumber)</span><br><span class="line"><span class="keyword">VALUES</span>(<span class="variable">@orderNumber</span>,<span class="string">&#x27;S18_1749&#x27;</span>, <span class="number">30</span>, <span class="string">&#x27;136&#x27;</span>, <span class="number">1</span>),</span><br><span class="line">      (<span class="variable">@orderNumber</span>,<span class="string">&#x27;S18_2248&#x27;</span>, <span class="number">50</span>, <span class="string">&#x27;55.09&#x27;</span>, <span class="number">2</span>); </span><br><span class="line">      </span><br><span class="line"><span class="comment">-- 5. commit changes    </span></span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br></pre></td></tr></table></figure>
<h2 id="锁的认识"><a href="#锁的认识" class="headerlink" title="锁的认识"></a>锁的认识</h2><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> show variables like <span class="string">&#x27;%isolation%&#x27;</span>;</span></span><br></pre></td></tr></table></figure>
<p><img src="image-20210929092405022.png" alt="image-20210929092405022"></p>
<p>版本8.0的数据库的默认事务隔离级别是可重复读(repeatable-read)</p>
<p>锁：计算机协调多个进程或线程并发访问某一资源的机制</p>
<p>加锁消耗资源，包括：获得锁、检测锁是否以及解除、释放锁等</p>
<h3 id="锁的类型"><a href="#锁的类型" class="headerlink" title="锁的类型"></a>锁的类型</h3><h4 id="自增锁（Auto-inc-Locks-table-level-lock）"><a href="#自增锁（Auto-inc-Locks-table-level-lock）" class="headerlink" title="自增锁（Auto-inc Locks; table-level lock）"></a>自增锁（Auto-inc Locks; table-level lock）</h4><p>查询当前数据的自增锁模式：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> show variables like <span class="string">&#x27;%innodb_autoinc_lock_mode%&#x27;</span>;</span></span><br></pre></td></tr></table></figure>
<p><img src="image-20210929093930764.png" alt="image-20210929093930764"></p>
<p>在MySQL8.0开始<code>innodb autoinc locak mode=2</code>是默认设置，8.0之前是1.</p>
<ul>
<li>0：traditonal每次insert语句都会产生表锁</li>
<li>1：consecutive simple insert会获得批量的锁，保证一批插入自增序列的连续性，插入之前提前释放锁。</li>
<li>interleaved：不会锁表，实时插入，并发最高</li>
</ul>
<p>自增锁是一种特殊的表级别锁（table-level lock），专门针对插入<code>AUTO_INCREMENT</code>类型的列。</p>
<h4 id="共享锁和排它锁（Shared-and-Exclusive-Locks）"><a href="#共享锁和排它锁（Shared-and-Exclusive-Locks）" class="headerlink" title="共享锁和排它锁（Shared and Exclusive Locks）"></a>共享锁和排它锁（Shared and Exclusive Locks）</h4><h5 id="并发控制"><a href="#并发控制" class="headerlink" title="并发控制"></a>并发控制</h5><p>并发控制可以解决临界资源操作时不一致的情况产生，保证数据一致性常见的手段就是锁和数据多版本。</p>
<h5 id="直接加锁"><a href="#直接加锁" class="headerlink" title="直接加锁"></a>直接加锁</h5><p>将被加锁的资源都被锁住，读取任务也无法执行直到锁释放。</p>
<h5 id="共享锁（Shared-Locks）简称S锁"><a href="#共享锁（Shared-Locks）简称S锁" class="headerlink" title="共享锁（Shared Locks）简称S锁"></a>共享锁（Shared Locks）简称S锁</h5><p>读取数据时候可以加S锁</p>
<p>允许并发事务读取一个资源。资源上存在S锁，任何其他事务都不能修改数据。一旦已经读取数据，便立即释放资源上的S锁，除非将事务隔离级别设置为可重复读或更高级别</p>
<h5 id="排它锁（Exclusive-Locks）简称为X锁"><a href="#排它锁（Exclusive-Locks）简称为X锁" class="headerlink" title="排它锁（Exclusive Locks）简称为X锁"></a>排它锁（Exclusive Locks）简称为X锁</h5><p>修改数据时候加X锁</p>
<p>可以防止并发事务对资源进行访问，其他事务不能读取或修改排它X锁锁定的数据。</p>
<p>共享锁之间可以并行，排它锁和共享锁之间互斥。</p>
<h5 id="死锁"><a href="#死锁" class="headerlink" title="死锁"></a>死锁</h5><h5 id="数据多版本"><a href="#数据多版本" class="headerlink" title="数据多版本"></a>数据多版本</h5><h5 id="InnoDB如何操作多版本控制"><a href="#InnoDB如何操作多版本控制" class="headerlink" title="InnoDB如何操作多版本控制"></a>InnoDB如何操作多版本控制</h5><p>redo：事务</p>
<h4 id="意向锁、记录锁和间隙锁"><a href="#意向锁、记录锁和间隙锁" class="headerlink" title="意向锁、记录锁和间隙锁"></a>意向锁、记录锁和间隙锁</h4><p>意向锁（Intention Locks；table-level lock）</p>
<p>一种特殊的表级锁，为了让InnoDB多粒度的锁能共存而设计的。</p>
<p>取得行的共享锁和排它锁之前需要先取得表的意向共享锁（IS）和意向排它锁（IX），都是系统自动添加和自动释放的。</p>
<p>意向锁就职值未来的某一个时刻事务可能要共享锁或排它锁，提前声明一个意向,，分两种：</p>
<h5 id="意向共享锁-（Intention-Shared-Lock）-IS"><a href="#意向共享锁-（Intention-Shared-Lock）-IS" class="headerlink" title="意向共享锁 （Intention Shared Lock） IS"></a>意向共享锁 （Intention Shared Lock） IS</h5><p>事务有意向对表中的某些行加S锁</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">column</span> <span class="keyword">FROM</span> <span class="keyword">table</span> ... LOCK <span class="keyword">IN</span> SHARE MODE;</span><br></pre></td></tr></table></figure>
<h6 id="意向排它锁（Intention-Exclusive-Lock）IX"><a href="#意向排它锁（Intention-Exclusive-Lock）IX" class="headerlink" title="意向排它锁（Intention Exclusive Lock）IX"></a>意向排它锁（Intention Exclusive Lock）IX</h6><p>事务有意向对表中的某些行加X锁</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">column</span> <span class="keyword">FROM</span> <span class="keyword">table</span> ... <span class="keyword">FOR</span> UPDATE;</span><br></pre></td></tr></table></figure>
<p>例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 要设置 IS 锁 --</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> A <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">1</span> lock <span class="keyword">in</span> share mode;</span><br><span class="line"><span class="comment">-- 要设置 IX 锁 --</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> A <span class="keyword">where</span> id <span class="operator">&gt;</span> <span class="number">0</span> <span class="keyword">for</span> update;</span><br></pre></td></tr></table></figure>
<p>事务1锁住了表A的其中id=1的一行，事务2要获取整个表的更新锁需要进行判断</p>
<ol>
<li><p>判断表A是否有表级锁</p>
</li>
<li><p>判断表A每一行是否有行级锁</p>
</li>
</ol>
<p>当数据量庞大时2判断低效，于是有意向锁协议</p>
<h5 id="意向锁协议"><a href="#意向锁协议" class="headerlink" title="意向锁协议"></a>意向锁协议</h5><p>事务要获取表A某些行的S锁必须要获取表A的IS锁</p>
<p>事务要获取表A某些行的X锁必须要获取表A的IX锁</p>
<p>上述2步骤：发现表A表IS锁，说明表A肯定有行级的S锁，因此，事务B申请写锁阻塞等待，判断效率极大提高</p>
<h5 id="记录锁（Record-Locks）"><a href="#记录锁（Record-Locks）" class="headerlink" title="记录锁（Record Locks）"></a>记录锁（Record Locks）</h5><p><code>SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;</code> 这一行则是使用了记录锁，不允许其他事务增删改</p>
<p>但是<code>SELECT c1 FROM t WHERE c1 = 10;</code>没有锁，走的是快照读。</p>
<p>记录锁本身不是锁定记录数据本身而是锁定索引记录，如果要锁的列没有索引，则会进行全表记录加锁。</p>
<h6 id="间隙锁（Gap-Locks）"><a href="#间隙锁（Gap-Locks）" class="headerlink" title="间隙锁（Gap Locks）"></a>间隙锁（Gap Locks）</h6><p><code>SELECT c1 FEOM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE;</code></p>
<p>插入 c1 为 15 的记录会被锁定不可执行</p>
<p>这种默认存在于可重复读的事务隔离级别中的锁，锁定被圈定的范围不允许 insert，防止不可重复读，上文说了我们的事务隔离级别都是读已提交，默认会产生不可重复读的问题</p>
<h4 id="临键锁和插入意向锁"><a href="#临键锁和插入意向锁" class="headerlink" title="临键锁和插入意向锁"></a>临键锁和插入意向锁</h4><p>临键锁（Next-Key Locks）</p>
<p>本质是记录锁加上gap锁，数据库可重复读事务隔离级别默认存在</p>
<h3 id="上锁"><a href="#上锁" class="headerlink" title="上锁"></a>上锁</h3><h4 id="加表锁"><a href="#加表锁" class="headerlink" title="加表锁"></a>加表锁</h4><p>MySQL常用的引擎有MyISAM和InnoDB，InnoDB是MySQL默认的引擎，MyISAM不支持行锁，InnoDB支持行锁和表锁。</p>
<p>隐式上锁（默认、自动加锁、自动释放）</p>
<p>在执行查询语句（SELECT ）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。</p>
<p>显示上锁（手动）</p>
<p>上共享锁（读锁）：<code>lock in share mode</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> column_name <span class="keyword">from</span> table_name <span class="keyword">where</span> lock <span class="keyword">in</span> share mode;</span><br></pre></td></tr></table></figure>
<p>上排它锁（写锁）：<code>for update</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> column_name <span class="keyword">from</span> table_name <span class="keyword">where</span> conditions <span class="keyword">for</span> update;</span><br></pre></td></tr></table></figure>
<p>解锁（手动）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 解锁所有锁表 --</span></span><br><span class="line">UNLOCK TABLES;</span><br></pre></td></tr></table></figure>
<h4 id="加行锁"><a href="#加行锁" class="headerlink" title="加行锁"></a>加行锁</h4><h4 id="排查表锁"><a href="#排查表锁" class="headerlink" title="排查表锁"></a>排查表锁</h4><p>查看表锁情况</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> <span class="keyword">OPEN</span> TABLES;</span><br></pre></td></tr></table></figure>
<p>表锁分析</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> STATUS <span class="keyword">LIKE</span> <span class="string">&#x27;table%&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p><img src="image-20211003112906566.png" alt="image-20211003112906566"></p>
<ul>
<li>Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），此值高说明存在着较严重的表级锁争用情况</li>
<li>Table_locks_immediate：产生表级锁定次数，不是可以立即获取锁的查询次数，每理解获取锁加1</li>
</ul>
<h4 id="排查行锁"><a href="#排查行锁" class="headerlink" title="排查行锁"></a>排查行锁</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> STATUS <span class="keyword">LIKE</span> <span class="string">&#x27;innodb_row_lock%&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p><img src="image-20211003122223144.png" alt="image-20211003122223144"></p>
<ul>
<li>Innodb_row_lock_current_waits：当前正在等待锁定的数量</li>
<li>Innodb_row_lock_time：从系统启动到现状锁定总时间长度</li>
<li>Innodb_row_lock_time_avg：每次等待锁花平均时间</li>
<li>Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间</li>
<li>Innodb_row_lock_waits：系统启动后到现在总共等待的次数</li>
</ul>
<p>优化建议</p>
<ol>
<li>尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。</li>
<li>合理设计索引，尽量缩小锁的范围</li>
<li>尽可能较少检索条件，避免间隙锁</li>
<li>尽量控制事务大小，减少锁定资源量和时间长度</li>
<li>尽可能低级别事务隔离</li>
</ol>
<h4 id="乐观锁与悲观锁"><a href="#乐观锁与悲观锁" class="headerlink" title="乐观锁与悲观锁"></a>乐观锁与悲观锁</h4><p>MyISAM引擎不支持事务，只有表锁，锁又分读锁和写锁，不考虑乐观锁和悲观锁的概念。</p>
<p>乐观锁和悲观锁不是解决并发控制的唯一手段（也可用消息中间件作为缓冲等等），乐观锁和悲观锁是一种概念。</p>
<p>乐观锁</p>
<p>从应用系统层面上做并发控制，去加锁。</p>
<p>实现乐观锁的常见方式：版本号 version</p>
<p>实现方式：在数据表中增加版本号字段，每次对一条数据做更新之前，先查出这条数据的版本好，每次更新数据都会对版本号进行更新。在更新时，把之前查出的版本号跟库中数据的版本号进行比对，如果相同，则说明这条数据没有被修改过，执行更新，如果比对的结果时不一致，则锁门这条数据已经被其他人修改过了，则不更新，客户端进行相应的操作提醒。</p>
<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1. 查询商品信息 --</span></span><br><span class="line">select status, version from t_goods where id = #&#123;id&#125;;</span><br><span class="line"><span class="comment">-- 2. 根据商品信息生成订单 --</span></span><br><span class="line"><span class="comment">-- 3. 修改商品status为2 --</span></span><br><span class="line">update t_goods</span><br><span class="line"><span class="keyword">set</span> status <span class="operator">=</span> <span class="number">2</span>, version <span class="operator">=</span> version <span class="operator">+</span> <span class="number">1</span></span><br><span class="line">where id = #&#123;id&#125; and version = #&#123;version&#125;;</span><br></pre></td></tr></table></figure>
<p>悲观锁</p>
<p>从数据库层面上做并发控制，去加锁。</p>
<p>悲观锁的实现方式有两种：共享锁（读锁）和排它锁（写锁）</p>
<p>共享锁（IS）锁，实现方式在SQL语句后加<code>LOCK IN SHARE MODE</code>, 即在符合条件的rows上都加了共享锁。</p>
<p>排他锁（IX）锁，实现方式在SQL语句后加<code>FOR UPDATE</code>, 即在符合调和的rows上都加了排他锁。</p>
<p>InnoDB有非锁定读（快照读并不需要加锁），for update之后并不会阻塞其他session的快照读取操作，出来显示加锁的查询操作。</p>
<p>MySQL InnoDB引擎默认的修改数据语句：update、delete、insert会自动给涉及到的数据加排他锁，select默认不会加任何锁类型。</p>
<p>补充</p>
<ol>
<li>MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁。</li>
<li>MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引时不会使用行级锁的，会使用表级锁把整张表锁住。</li>
</ol>
<h2 id="触发器"><a href="#触发器" class="headerlink" title="触发器"></a>触发器</h2><p>触发器是一个存储程序，它对相关表中发生的插入、更新或删除等事件自动调用。</p>
<p>MySQL支持响应INSERT、UPDATE、DELETE事件而调用的触发器</p>
<p>SQL标准定义了两类型的触发器：行级触发器和语句级触发器</p>
<ul>
<li>行级触发器是为每条插入、更新或删除的记录激活的。</li>
<li>语句级触发器对每个事务执行一次</li>
</ul>
<blockquote>
<p>MySQL只支持行级触发器，不支持语句级触发器</p>
</blockquote>
<h3 id="创建触发器"><a href="#创建触发器" class="headerlink" title="创建触发器"></a>创建触发器</h3><p>CREATE TRIGGER语句创建一个新的触发器</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> trigger_name</span><br><span class="line">&#123;BEFORE | AFTER&#125; &#123;INSERT | UPDATE | DELETE&#125;</span><br><span class="line"><span class="keyword">ON</span> table_name <span class="keyword">FOR</span> <span class="keyword">EACH</span> <span class="type">ROW</span></span><br><span class="line">trigger_body;</span><br></pre></td></tr></table></figure>
<ul>
<li><code>CREATE TRIGGER</code> 后面指定要创建的触发器的名称。名称在数据库中必须是唯一的。</li>
<li>指定触发器的动作事件，<code>BEFORE</code>或<code>AFTER</code>,表示在每条记录被修改之前或之后调用改触发器。</li>
<li>指定激活触发器的操作 <code>INSERT</code>、<code>UPDATE</code>、<code>DELETE</code></li>
<li>在<code>ON</code> 后面指定触发器所属的表的名称</li>
<li>最后，指定触发器激活时要执行的语句，可使用<code>BEGIN END</code>复合语句执行多条语句。</li>
</ul>
<p>区分DML启动之前和之后的列的值，可使用<code>NEW</code>和<code>OLD</code>修饰</p>
<div class="table-container">
<table>
<thead>
<tr>
<th>Trigger</th>
<th>OLD</th>
<th>NEW</th>
</tr>
</thead>
<tbody>
<tr>
<td>INSERT</td>
<td>NO</td>
<td>YES</td>
</tr>
<tr>
<td>UPDATE</td>
<td>YES</td>
<td>YES</td>
</tr>
<tr>
<td>DELETE</td>
<td>YES</td>
<td>NO</td>
</tr>
</tbody>
</table>
</div>
<p><code>SHOW TRIGGERS</code>显示当前数据库中的所有触发器</p>
<h3 id="删除触发器"><a href="#删除触发器" class="headerlink" title="删除触发器"></a>删除触发器</h3><p><code>DROP TRIGGRT</code>语句删除一个触发器</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TRIGGER</span> [IF <span class="keyword">EXISTS</span>] [schema_name.]trigger_name;</span><br></pre></td></tr></table></figure>
<ul>
<li><code>DROP TRIGGER</code> 后面指定要删除的触发器的名称</li>
</ul>
<h3 id="BEFORE-INSERT-Trigger"><a href="#BEFORE-INSERT-Trigger" class="headerlink" title="BEFORE INSERT Trigger"></a>BEFORE INSERT Trigger</h3><p><code>BEFORE INSERT</code>触发器是在表发生插入事件之前自动触发的。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> trigger_name</span><br><span class="line">	BEFORE <span class="keyword">INSERT</span></span><br><span class="line">	<span class="keyword">ON</span> table_name <span class="keyword">FOR</span> <span class="keyword">EACH</span> <span class="type">ROW</span></span><br><span class="line">trigger_body;</span><br></pre></td></tr></table></figure>
<p>如果在触发器主体中有多条语句，必须使用<code>BEGIN END</code>块并改变默认的分隔符：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER $$</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> trigger_name</span><br><span class="line">	BEFORE <span class="keyword">INSERT</span></span><br><span class="line">	<span class="keyword">ON</span> table_name <span class="keyword">FOR</span> <span class="keyword">EACH</span> <span class="type">ROW</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">	<span class="comment">-- statements</span></span><br><span class="line"><span class="keyword">END</span>$$</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>
<h3 id="创建多个触发器"><a href="#创建多个触发器" class="headerlink" title="创建多个触发器"></a>创建多个触发器</h3><p>MySQL 5.7.2之前，只能为一个表中的事件创建一个触发器；MySQL 5.7.2之后取消校址，允许为一个给定的表创建多个具有相同事件和行动时间的触发器。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER $$</span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> trigger_name</span><br><span class="line">&#123;BEFORE|AFTER&#125;&#123;INSERT|UPDATE|DELETE&#125; </span><br><span class="line"><span class="keyword">ON</span> table_name <span class="keyword">FOR</span> <span class="keyword">EACH</span> <span class="type">ROW</span> </span><br><span class="line">&#123;FOLLOWS|PRECEDES&#125; existing_trigger_name</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">    <span class="comment">-- statements</span></span><br><span class="line"><span class="keyword">END</span>$$</span><br><span class="line"></span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>
<ul>
<li>FOLLOWS：允许新的触发器在一个现有的触发器之后激活</li>
<li>PRECEDES：允许新的触发器在现有触发器之前激活</li>
</ul>
<h3 id="MySQL视图处理算法"><a href="#MySQL视图处理算法" class="headerlink" title="MySQL视图处理算法"></a>MySQL视图处理算法</h3><p>CREATE VIEW 和 ALTER VIEW语句中有一个可选的子句：<code>ALGORITHM</code> </p>
<p>取三个值：<code>MERGE</code> 、<code>TEMPTABLE</code> 、<code>UNDEFINE</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CREATE [OR REPLACE][ALGORITHM = &#123;MERGE | TEMPTABLE | UNDEFINED&#125;] VIEW </span><br><span class="line">	view_name[(column_list)]</span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">	<span class="keyword">select</span><span class="operator">-</span>statement;</span><br></pre></td></tr></table></figure>
<p>MERGE</p>
<ul>
<li>首先，将输入查询与视图定义中的SELECT语句合并为一个单一的查询</li>
<li>然后，执行合并后的查询以返回结果集</li>
</ul>
<blockquote>
<p>将输入查询和视图定义的SELECT语句合并成一个单一的查询被称为视图解析</p>
</blockquote>
<p>TEMPTABLE</p>
<p>向TEMPTABLE视图发出查询时，MySQL执行步骤：</p>
<ul>
<li>首先，创建一个临时表来存储视图定义的中SELECT的结果</li>
<li>然后，针对该临时表执行输入查询</li>
</ul>
<p>效率比MERGE算法低，不能被更新</p>
<p>UNDEFINED</p>
<p>默认算法</p>
<h3 id="ALTER-VIEW语句"><a href="#ALTER-VIEW语句" class="headerlink" title="ALTER VIEW语句"></a>ALTER VIEW语句</h3><p>ALTER VIEW语句改变了一个现有视图的定义。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> </span><br><span class="line">	[ALGORITHM = &#123;UNDEFINED | MERGE | TEMPTABLE&#125;]</span><br><span class="line">	<span class="keyword">VIEW</span> view_name [(column_list)]</span><br><span class="line">	<span class="keyword">as</span> select_statement;</span><br></pre></td></tr></table></figure>
<h3 id="可更新视图"><a href="#可更新视图" class="headerlink" title="可更新视图"></a>可更新视图</h3><p>视图不仅可查询，也可更新。</p>
<p>但是创建一个可更新的视图，定义该视图的select语句不能包含聚合函数、distinct、group by子句、having子句、union、union all子句、外连接、子查询、在from对不可更新的视图引用、只引用字面意义的值、对基表的任何列的多次引用、用temptable算法创建视图，不能更新这个视图。</p>
<h3 id="可更行视图操作"><a href="#可更行视图操作" class="headerlink" title="可更行视图操作"></a>可更行视图操作</h3><p>检查可更新的视图信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">    table_name, </span><br><span class="line">    is_updatable</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    information_schema.views</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    table_schema <span class="operator">=</span> <span class="string">&#x27;classicmodels&#x27;</span>;</span><br></pre></td></tr></table></figure>
<h3 id="WITH-CHECK-OPTION子句"><a href="#WITH-CHECK-OPTION子句" class="headerlink" title="WITH CHECK OPTION子句"></a>WITH CHECK OPTION子句</h3><p><code>WITH CHECK OPTION</code>防止视图更新或插入通过它不可见的行。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> [<span class="keyword">OR</span> REPLACE <span class="keyword">VIEW</span>] view_name </span><br><span class="line"><span class="keyword">AS</span></span><br><span class="line">  select_statement</span><br><span class="line">  <span class="keyword">WITH</span> <span class="keyword">CHECK</span> OPTION;</span><br></pre></td></tr></table></figure>
<h3 id="SHOW-VERSION"><a href="#SHOW-VERSION" class="headerlink" title="SHOW VERSION"></a>SHOW VERSION</h3><p>使用show full tables语句，显示当前数据库中的所有视图</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> <span class="keyword">FULL</span> TABLES</span><br><span class="line">[&#123;FROM | IN&#125; database_name]</span><br><span class="line"><span class="keyword">WHERE</span> table_type <span class="operator">=</span> <span class="string">&#x27;VIEW&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p>使用information_schema数据库</p>
<p>infoemation_schema数据库提供对MySQL数据库元数据的访问。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> information_schema.tables;</span><br></pre></td></tr></table></figure>
<ul>
<li>table_schema：存储视图（表）的模式或数据库</li>
<li>table_name：存储视图（表）的名称</li>
<li>table_type：存储表的类型<ul>
<li>BASE TABLE：表</li>
<li>VIEW：视图</li>
<li>SYSTEM VIEW ：INFORMATION_SCHEMA表</li>
</ul>
</li>
</ul>
<h3 id="重命名视图"><a href="#重命名视图" class="headerlink" title="重命名视图"></a>重命名视图</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">RENAME <span class="keyword">TABLE</span> original_view_name </span><br><span class="line"><span class="keyword">TO</span> new_view_name;</span><br></pre></td></tr></table></figure>
<h3 id="删除视图"><a href="#删除视图" class="headerlink" title="删除视图"></a>删除视图</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">VIEW</span> [IF <span class="keyword">EXISTS</span>] view_name;</span><br></pre></td></tr></table></figure>
<h3 id="WITH-CASCADED-CHECK-OPTION"><a href="#WITH-CASCADED-CHECK-OPTION" class="headerlink" title="WITH CASCADED CHECK OPTION"></a>WITH CASCADED CHECK OPTION</h3><p>当用with check option子句创建视图，对该视图发出DML语句，MySQL会将检查确保被改变的行符合该视图的定义。</p>
<p>检查的范围，MySQL提供两个选项：<code>LOCAL</code>和<code>CASCADED</code></p>
<p>默认使用<code>CASCADED</code>, 会递归检查底层视图的规则</p>
<p><img src="image-20211011115009725.png" alt="image-20211011115009725"></p>
<h3 id="WITH-LOCAL-CHECK-OPTION"><a href="#WITH-LOCAL-CHECK-OPTION" class="headerlink" title="WITH LOCAL CHECK OPTION"></a>WITH LOCAL CHECK OPTION</h3><p>如果一个视图使用WITH LOCAL CHECK OPTION，MySQL会检查<code>WITH LOCAL CHECK OPTION</code>和<code>WITH CASCADED CHECK OPTION</code>的视图的规则，</p>
<p>与<code>WITH CASCADED CHECK OPTION</code>的视图不同，MySQL检查的是所有依赖的视图的规则。</p>
<h3 id="Managing-Views-in-MySQL"><a href="#Managing-Views-in-MySQL" class="headerlink" title="Managing Views in MySQL"></a>Managing Views in MySQL</h3><p>显示视图定义</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> <span class="keyword">CREATE</span> <span class="keyword">VIEW</span> [database_name].[view_name];</span><br></pre></td></tr></table></figure>
<p>修改视图</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span></span><br><span class="line"> [ALGORITHM =  &#123;MERGE | TEMPTABLE | UNDEFINED&#125;]</span><br><span class="line">  <span class="keyword">VIEW</span> [database_name].  [view_name]</span><br><span class="line">   <span class="keyword">AS</span> </span><br><span class="line"> [<span class="keyword">SELECT</span>  statement]</span><br></pre></td></tr></table></figure>
<p>删除视图</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">VIEW</span> [IF <span class="keyword">EXISTS</span>] [database_name].[view_name]</span><br></pre></td></tr></table></figure>


        <hr>
        <!-- Pager -->
        <ul class="pager">
          
          <li class="previous">
            <a href="/cn/大数据/word2vec/" data-toggle="tooltip" data-placement="top" title="">&larr; Previous Post</a>
          </li>
          
          
          <li class="next">
            <a href="/cn/mysql/Redis初学/" data-toggle="tooltip" data-placement="top" title="Redis初学">Next Post &rarr;</a>
          </li>
          
        </ul>

        
        <!-- tip start -->
        <!-- tip -->
<!-- tip start -->
<div class="tip">
  <p>
    
      如果您喜欢此博客或发现它对您有用，则欢迎对此发表评论。 也欢迎您共享此博客，以便更多人可以参与。 如果博客中使用的图像侵犯了您的版权，请与作者联系以将其删除。 谢谢 ！
    
  </p>
</div>
<!-- tip end -->

        <!-- tip end -->
        

        
        <hr>

        <!-- comments start -->
        <!-- 1. gitalk comment -->


<!-- 2. gitment comment -->


<!-- 3. disqus comment -->


        <!-- comments end -->
        <hr>

      </div>

      <!-- Catalog: Tabe of Content -->
      <!-- Table of Contents -->



      <!-- Sidebar Container -->
      <div class="
                col-lg-8 col-lg-offset-1
                col-md-10 col-md-offset-1
                sidebar-container">

        <!-- Featured Tags -->
        
        <section>
          <!-- no hr -->
          <h5>
            <a href="/tags/">特色标签</a>
          </h5>
          <div class="tags">
            
            <a class="tag" href="/tags/#数据库" title="数据库">数据库</a>
            
            <a class="tag" href="/tags/#mysql" title="mysql">mysql</a>
            
            <a class="tag" href="/tags/#SQL" title="SQL">SQL</a>
            
          </div>
        </section>
        

        <!-- Friends Blog -->
        
        <hr>
        <h5>链友</h5>
        <ul class="list-inline">

          
        </ul>
        
      </div>
    </div>
  </div>
</article>



<!-- anchorjs start -->
<!-- async load function -->
<!-- anchor-js, Doc:http://bryanbraun.github.io/anchorjs/ -->
<script type="text/javascript">
  // async load function
  function async (u, c) {
    var d = document,
      t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) {
      o.addEventListener('load', function(e) {
        c(null, e);
      }, false);
    }
    s.parentNode.insertBefore(o, s);
  };
</script>
<script type="text/javascript">
  //anchor-js, Doc:http://bryanbraun.github.io/anchorjs/
  async ("https://cdn.bootcss.com/anchor-js/1.1.1/anchor.min.js", function() {
    anchors.options = {
      visible: 'hover',
      placement: 'left',
      // icon: 'ℬ'
      icon: '❡'
    };
    anchors.add().remove('.intro-header h1').remove('.subheading').remove('.sidebar-container h5');
  });
</script>
<style>
  /* place left on bigger screen */
  @media all and (min-width: 800px) {
    .anchorjs-link {
      position: absolute;
      left: -0.75em;
      font-size: 1.1em;
      margin-top: -0.1em;
    }
  }
</style>

<!-- anchorjs end -->



		<!-- Footer (contains ThemeColor、viewer) -->
		<!-- Footer -->
<footer>
  <div class="container">
    <div class="row">
      <div class="col-lg-8 col-lg-offset-2 col-md-10 col-md-offset-1">
        <ul class="list-inline text-center">
          

          

          

          

          

          

          

          

        </ul>
        <p class="copyright text-muted">
          Copyright &copy;
          zhuang
          2021
          <br>
          Theme by
          <a target="_blank" rel="noopener" href="http://beantech.org">BeanTech</a>
          <span style="display: inline-block; margin: 0 5px;">
            <i class="fa fa-heart"></i>
          </span>
          re-Ported by
          <a target="_blank" rel="noopener" href="https://v-vincen.life/">Live My Life</a>
        </p>
      </div>
    </div>
  </div>
</footer>

<a id="rocket" href="#top" class=""></a>


  <!-- jQuery -->
  <script type="text/javascript" src="/js/jquery.min.js"></script>
  <!-- Bootstrap Core JavaScript -->
  <script type="text/javascript" src="/js/bootstrap.min.js"></script>
  <!-- Custom Theme JavaScript -->
  <script type="text/javascript" src="/js/hux-blog.min.js"></script>
  <!-- catalog -->
  <script async="true" type="text/javascript" src="/js/catalog.js"></script>
  <!-- totop(rocket) -->
  <script async="true" type="text/javascript" src="/js/totop.js"></script>

  
    <!-- Busuanzi JavaScript -->
    <script async="async" src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
  

  
    <!-- Scroll start -->
    <script async="async" type="text/javascript" src="/js/scroll.js"></script>
    <!-- Scroll end -->
  

  

  

  
    <!-- ribbon -->
    <script type="text/javascript" src="/js/ribbonDynamic.js"></script>
  

  






  <!-- viewer start -->
  <!-- viewer start (Picture preview) -->
  
    <script async="async" type="text/javascript" src="/js/viewer/viewer.min.js"></script>
    <script async="async" type="text/javascript" src="/js/viewer/pic-viewer.js"></script>
  

  <!-- viewer end -->


<script>
  // async load function
  function async (u, c) {
    var d = document,
      t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) {
      o.addEventListener('load', function (e) {
        c(null, e);
      }, false);
    }
    s.parentNode.insertBefore(o, s);
  }

  // fastClick.js
  async ("https://cdn.bootcss.com/fastclick/1.0.6/fastclick.min.js", function () {
    var $nav = document.querySelector("nav");
    if ($nav)
      FastClick.attach($nav);
    }
  )
</script>

<!-- Because of the native support for backtick-style fenced code blocks right within the Markdown is landed in Github Pages, From V1.6, There is no need for Highlight.js, so Huxblog drops it officially. -
https://github.com/blog/2100-github-pages-now-faster-and-simpler-with-jekyll-3-0 - https://help.github.com/articles/creating-and-highlighting-code-blocks/ -->
<!-- <script> async ("http://cdn.bootcss.com/highlight.js/8.6/highlight.min.js", function () { hljs.initHighlightingOnLoad(); }) </script> <link href="http://cdn.bootcss.com/highlight.js/8.6/styles/github.min.css" rel="stylesheet"> -->

<!-- jquery.tagcloud.js -->
<!-- <script> // only load tagcloud.js in tag.html if ($('#tag_cloud').length !== 0) { async ("http://yoursite-url/js/jquery.tagcloud.js", function () { $.fn.tagcloud.defaults = { // size: { start: 1, end: 1, unit: 'em' }, color: {
start: '#bbbbee', end: '#0085a1' } }; $('#tag_cloud a').tagcloud(); }) } </script> -->


		<!-- Search -->
		
		<div class="popup search-popup local-search-popup">
  <span class="popup-btn-close">
    ESC
  </span>
  <div class="container">
    <div class="row">
      <!-- <div class="col-md-9 col-md-offset-1"> -->
      <div class="col-lg-9 col-lg-offset-1 col-md-10 col-md-offset-1 local-search-content">

        <div class="local-search-header clearfix">

          <div class="local-search-input-wrapper">
            <span class="search-icon">
              <i class="fa fa-search fa-lg" style="margin: 25px 10px 25px 20px;"></i>
            </span>
            <input autocomplete="off" placeholder="搜索..." type="text" id="local-search-input">
          </div>
        </div>
        <div id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>


  
    <script src="/js/ziploader.js"></script>
  
  <script>
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.json";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    // monitor main search box;
    var onPopupClose = function (e) {
      $('.popup').fadeOut(300);
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $('.popup').fadeIn(300);
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }
    // get search zip version
    $.get('/searchVersion.json?t=' + (+new Date()), function (res) {
      if (localStorage.getItem('searchVersion') !== res) {
        localStorage.setItem('searchVersion', res);
        initSearchJson();
      }
    });

    function initSearchJson() {
      initLoad(['/search.flv'], {
        loadOptions: {
          success: function (obj) {
            localStorage.setItem('searchJson', obj['search.json'])
          },
          error: function (e) {
            return console.log(e)
          }
        },
        returnOptions: {
          'json': TYPE_TEXT
        },
        mimeOptions: {
          'json': 'application/json'
        }
      })
    }
    // search function;
    var searchFunc = function (search_id, content_id) {
      'use strict';
      isfetched = true;
      var datas = JSON.parse(localStorage.getItem('searchJson'));
      // console.log(search_id)
      var input = document.getElementById(search_id);
      var resultContent = document.getElementById(content_id);
      var inputEventFunction = function () {
        var searchText = input.value.trim().toLowerCase();
        var keywords = searchText.split(/[\s\-]+/);
        if (keywords.length > 1) {
          keywords.push(searchText);
        }
        var resultItems = [];
        if (searchText.length > 0) {
          // perform local searching
          datas.forEach(function (data) {
            var isMatch = false;
            var hitCount = 0;
            var searchTextCount = 0;
            var title = data.title
              ? data.title.trim()
              : '';
            var titleInLowerCase = title.toLowerCase();
            var content = data.content
              ? data.content.trim().replace(/<[^>]+>/g, "")
              : '';
            var contentInLowerCase = content.toLowerCase();
            var articleUrl = decodeURIComponent(data.url);

            var date = data.date;
            var dateTime = date.replace(/T/, " ").replace(/.000Z/, "");
            var imgUrl = data.header_img;
            


            var indexOfTitle = [];
            var indexOfContent = [];
            // only match articles with not empty titles
            keywords.forEach(function (keyword) {
              function getIndexByWord(word, text, caseSensitive) {
                var wordLen = word.length;
                if (wordLen === 0) {
                  return [];
                }
                var startPosition = 0,
                  position = [],
                  index = [];
                if (!caseSensitive) {
                  text = text.toLowerCase();
                  word = word.toLowerCase();
                }
                while ((position = text.indexOf(word, startPosition)) > -1) {
                  index.push({position: position, word: word});
                  startPosition = position + wordLen;
                }
                return index;
              }
              indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
              indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
            });
            if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
              isMatch = true;
              hitCount = indexOfTitle.length + indexOfContent.length;
            }
            // show search results
            if (isMatch) {
              // sort index by position of keyword
              [indexOfTitle, indexOfContent].forEach(function (index) {
                index.sort(function (itemLeft, itemRight) {
                  if (itemRight.position !== itemLeft.position) {
                    return itemRight.position - itemLeft.position;
                  } else {
                    return itemLeft.word.length - itemRight.word.length;
                  }
                });
              });
              // merge hits into slices
              function mergeIntoSlice(text, start, end, index) {
                var item = index[index.length - 1];
                var position = item.position;
                var word = item.word;
                var hits = [];
                var searchTextCountInSlice = 0;
                while (position + word.length <= end && index.length != 0) {
                  if (word === searchText) {
                    searchTextCountInSlice++;
                  }
                  hits.push({position: position, length: word.length});
                  var wordEnd = position + word.length;
                  // move to next position of hit
                  index.pop();
                  while (index.length != 0) {
                    item = index[index.length - 1];
                    position = item.position;
                    word = item.word;
                    if (wordEnd > position) {
                      index.pop();
                    } else {
                      break;
                    }
                  }
                }
                searchTextCount += searchTextCountInSlice;
                return {hits: hits, start: start, end: end, searchTextCount: searchTextCountInSlice};
              }
              var slicesOfTitle = [];
              if (indexOfTitle.length != 0) {
                slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
              }
              var slicesOfContent = [];
              while (indexOfContent.length != 0) {
                var item = indexOfContent[indexOfContent.length - 1];
                var position = item.position;
                var word = item.word;
                // cut out 100 characters
                var start = position - 20;
                var end = position + 80;
                if (start < 0) {
                  start = 0;
                }
                if (end < position + word.length) {
                  end = position + word.length;
                }
                if (end > content.length) {
                  end = content.length;
                }
                slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
              }
              // sort slices in content by search text's count and hits' count
              slicesOfContent.sort(function (sliceLeft, sliceRight) {
                if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                  return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                  return sliceRight.hits.length - sliceLeft.hits.length;
                } else {
                  return sliceLeft.start - sliceRight.start;
                }
              });
              // select top N slices in content
              var upperBound = parseInt('1');
              if (upperBound >= 0) {
                slicesOfContent = slicesOfContent.slice(0, upperBound);
              }
              // highlight title and content
              function highlightKeyword(text, slice) {
                var result = '';
                var prevEnd = slice.start;
                slice.hits.forEach(function (hit) {
                  result += text.substring(prevEnd, hit.position);
                  var end = hit.position + hit.length;
                  result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                  prevEnd = end;
                });
                result += text.substring(prevEnd, slice.end);
                return result;
              }
              var resultItem = '';

              // if (slicesOfTitle.length != 0) {   resultItem += "<li><a target='_blank' href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>"; } else {   resultItem += "<li><a target='_blank' href='" +
              // articleUrl + "' class='search-result-title'>" + title + "</a>"; } slicesOfContent.forEach(function (slice) {   resultItem += "<a target='_blank' href='" + articleUrl + "'><p class=\"search-result\">" + highlightKeyword(content, slice) +
              // "...</p></a>"; }); resultItem += "</li>";

              if (slicesOfTitle.length != 0) {
                resultItem += "<a target='_blank' href='" + articleUrl + "' class='search-result'><div class='search-result-left'><div class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</div><time class='search-result-date'>" + dateTime + "</time>";
              } else {
                resultItem += "<a target='_blank' href='" + articleUrl + "' class='search-result'><div class='search-result-left'><div class='search-result-title'>" + title + "</div><time class='search-result-date'>" + dateTime + "</time>";
              }
              slicesOfContent.forEach(function (slice) {
                resultItem += "<p class=\"search-result-content\">" + highlightKeyword(content, slice) + "...</p>";
              });
              resultItem += "</div><div class='search-result-right'><img class='media-image' src='" + imgUrl + "' width='64px' height='48px'></img></div></a>";

              resultItems.push({item: resultItem, searchTextCount: searchTextCount, hitCount: hitCount, id: resultItems.length});
            }
          })
        };

        if (keywords.length === 1 && keywords[0] === "") {
          resultContent.innerHTML = '<div id="no-result"></div>'
        } else if (resultItems.length === 0) {
          resultContent.innerHTML = '<div id="no-result"></div>'
        } else {
          resultItems.sort(function (resultLeft, resultRight) {
            if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
              return resultRight.searchTextCount - resultLeft.searchTextCount;
            } else if (resultLeft.hitCount !== resultRight.hitCount) {
              return resultRight.hitCount - resultLeft.hitCount;
            } else {
              return resultRight.id - resultLeft.id;
            }
          });
          var searchResultList = '<div class=\"search-result-list\">';
          resultItems.forEach(function (result) {
            searchResultList += result.item;
          })
          searchResultList += "</div>";
          resultContent.innerHTML = searchResultList;
        }
      }
      if ('auto' === 'auto') {
        input.addEventListener('input', inputEventFunction);
      } else {
        $('.search-icon').click(inputEventFunction);
        input.addEventListener('keypress', function (event) {
          if (event.keyCode === 13) {
            inputEventFunction();
          }
        });
      }
      // remove loading animation
      $('body').css('overflow', '');
      proceedsearch();
    }
    // handle and trigger popup window;
    $('.popup-trigger').click(function (e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc('local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });
    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function (e) {
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 && $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });

    document.addEventListener('mouseup', (e) => {
      var _con = document.querySelector(".local-search-content");
      if (_con) {
        if (!_con.contains(e.target)) {
          onPopupClose();
        }
      }
    });
  </script>


		
	<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
        tex2jax: {
            inlineMath: [ ["$","$"], ["\\(","\\)"] ],
            skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
            processEscapes: true
        }
    });
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax();
        for (var i = 0; i < all.length; ++i)
            all[i].SourceElement().parentNode.className += ' has-jax';
    });
</script>
<script src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
</body>
</html>
